1. The university registrar wants to review subjects that require more study hours than the average. Retrieve the subjects where the subject hours exceed the average hours of all subjects.
WITH AverageStudyHours AS (SELECT AVG(Hours) AS avg_hours FROM subjects) SELECT Subject_name FROM subjects, AverageStudyHours WHERE Hours > AverageStudyHours.avg_hours
2. The academic coordinator wants to identify subjects with the least number of teaching hours. Write a SQL query to find the SID and minimum number of hours as Min_Hours for each subject.
SELECT SID, MIN(Hours) AS Min_Hours FROM subjects GROUP BY SID;
3. Students are curious about job opportunities with attractive packages. Display the job roles of students placed where Packages offered by the Companies are greater than 5 Lakh.
SELECT S.Job_role FROM Students S JOIN Companies C ON S.CID = C.CID WHERE S.Package > 500000;
4. A university wants to reward lecturers who teach subjects either in the 5th semester or subjects with more than 45 hours. Identify the names of lecturers meeting these criteria.
SELECT L.First_name FROM Lecturers L JOIN Subjects S ON L.SID = S.SID WHERE (S.Semester = 5 OR S.Hours > 45);
5. Curriculum planners want to calculate the average number of teaching hours per subject to optimize course schedules. Write a SQL query to select SID, and the average Hours as Avg_Hours grouped by SID.
SELECT SID, AVG(Hours) AS Avg_Hours FROM Subjects GROUP BY SID;
6. The placement cell is analyzing the average package offered by each company. Write a SQL query to select CID and find the average package as Avg_Package for each company.
SELECT CID, AVG(package) AS Avg_Package FROM Students GROUP BY CID;
7. The academic dean wants to identify the highest CGPA in each branch to reward top-performing students. Write a SQL query to select Branch ID (BID) and find the maximum CGPA as Max_CGPA for each branch ID.
SELECT Branch, MAX(CGPA) AS Max_CGPA FROM students GROUP BY Branch;
8. The placement cell wants to identify the highest package offered to students grouped by college and branch for a success report. Write a SQL query to select College, Branch, and the maximum package as Max_Package grouped by college and branch.
SELECT College, Branch, MAX(package) AS Max_Package FROM Students GROUP BY College, Branch;
9. Academic administrators need to know the total number of students in each combination of college, branch, and semester for enrollment planning. Write a SQL query to select College, Branch, Semester, and total student as Total_Students grouped by these columns.
SELECT College, Branch, Semester, COUNT(*) AS Total_Students FROM Students GROUP BY College, Branch, Semester;
10. A recruitment firm is interested in companies offering above-average packages to students. So could you help in listing the names of companies that provide packages higher than the average package offered by all companies.
SELECT Company_name FROM Companies WHERE package > (SELECT AVG(package) FROM Companies);
11. The researcher wants to know about subjects taught by assistant professors or subjects where the semester is less than 5. Retrieve the subject names based on these conditions.
SELECT s.Subject_name FROM Subjects s JOIN Lecturers l ON s.SID = l.SID WHERE (l.Designation = 'Assistant professor' OR s.Semester < 5) ORDER BY s.Subject_name;
12. The college administration wants to understand the student distribution across various branches to allocate resources effectively. Write a SQL query to display branch name and find the count of students as Total_Students in each branch.
SELECT b.Branch_name, COUNT(s.Usn) AS Total_Students FROM branches b JOIN students s ON b.BID = s.Branch GROUP BY b.Branch_name;
13. The exam committee is recognizing outstanding performance in each subject. Write a SQL query to display the subject name and find the maximum Test1 score as Max_Test1_Score for each subject.
SELECT s.Subject_name,MAX(m.Test1) AS Max_Test1_Score FROM Marks m JOIN Subjects s ON m.SID = s.SID GROUP BY s.Subject_name ORDER BY s.Subject_name;
14. The exam committee is evaluating the average performance in Test1 for each subject. Write a SQL query to select SID and find the average Test1 score as Avg_Test1_Score for each subject.
SELECT m.SID, AVG(m.Test1) AS Avg_Test1_Score FROM Marks m GROUP BY m.SID ORDER BY m.SID;